package cn.edu.nit.sys.mapper;

import cn.edu.nit.sys.entity.Score;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @Author 罗文斌
 * @Date 2023/5/10
 * @Version 1.0.0
 */
@Mapper
public interface ScoreMapper extends BaseMapper<Score> {

    @Select("select a.grade from sys_subject a " +
            "left join sys_teacher b on a.teacher_id = b.id " +
            "where b.uid = #{id} GROUP BY a.grade order by a.grade asc")
    List<String> getGradeList(String id);

    @Select("select a.id, a.name from sys_subject a " +
            "left join sys_teacher b on a.teacher_id = b.id " +
            "where b.uid = #{id}")
    List<Map<String, String>> getSubjectList(String id);

    Page<Score> getList(Page<Score> page, String siftId, String siftSubject, String siftTime);

    @Select("select b.id, b.name from sys_subject a " +
            "left join sys_student b  on a.clazz rlike concat(b.major, b.clazz, '班') and a.grade = b.grade " +
            "where a.id = #{subjectId} " +
            "and b.name like concat('%', #{name}, '%') " +
            "and b.id not in (select student_id from sys_score where subject_id = #{subjectId})")
    List<Map<String, String>> getStudentList(String subjectId, String name);

    @Select("select a.id, a.score, c.name as subject, c.year, c.score as credit from sys_score a " +
            "left join sys_student b on a.student_id = b.id " +
            "left join sys_subject c on c.id = a.subject_id " +
            "where b.uid = #{id} and a.id is not null order by a.create_time desc")
    List<Map<String, String>> getUserScore(String id);
}
